// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using System.Text;
using Kailua.net.windward.utils;
using Microsoft.Win32;

// bugbug
// 3: is there a way to enum servers yet?  (No. - Tomas)
// 3: get schema owner for tables, views, stored procedures
// 4: get stored procedures variables (type & description)  (DONE: there are no fields for this; the routine body must be parsed. - Tomas)
// this may help - http://msdn.microsoft.com/en-us/library/ms254969(VS.80).aspx

namespace net.windward.utils.ado.MySql
{
	/// <summary>
	/// This is similiar to DbProviderFactory and provides additional functionality for each provider. All properties to list
	/// if a provider can perform any task in it's child classes is provider here, not in the child classes. This
	/// class can return IWrServer objects for a given server/instance that exists on a computer. The ODBC/OleDb
	/// concept of a Provider (which is different from a DbProviderFactory) is handled as a property set in the
	/// WrProviderFactory class so all other cases do not need to implement a class for that layer.
	/// This is called WrVendor instead of WrProvider because it is different from DbProviderFactory and because
	/// we use the term Provider for the ODBC/OleDb providers.
	/// This is the class for the MySql database.
	/// </summary>
	public class WrMySqlVendor : WrVendor
	{
		/// <summary>
		/// The DbProviderFactory class for this vendor
		/// </summary>
		public const string FactoryClass = "MySql.Data.MySqlClient";
		
		/// <summary>
		/// The suggested name for this vendor.
		/// </summary>
		public const string FactoryName = "MySql";

		/// <summary>
		/// Creates a MySql vendor object.
		/// </summary>
		public WrMySqlVendor()
			: base(FactoryName, "The standard MySql connector", FactoryClass)
		{
			syntax = new WrMySqlSyntax(providerFactory);
		}

		/// <summary>
		/// Creates a MySql vendor object.
		/// </summary>
		/// <param name="name">The friendly name of the provider.</param>
		/// <param name="description">The description of this provider (optional).</param>
		/// <param name="providerClass">The class of the provider.</param>
		public WrMySqlVendor(string name, string description, string providerClass)
			: base(name, description, providerClass)
		{
			syntax = new WrMySqlSyntax(providerFactory);
		}

		#region WrVendor properties

		/// <summary>
		/// True if can enumerate running servers from this vendor. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateServers
		{
			get
			{
				if (providerFactory == null)
					return false;
				return providerFactory.CanCreateDataSourceEnumerator;
			}
		}

		/// <summary>
		/// True if can query metadata from the database.
		/// </summary>
		public override bool CanQueryMetadata
		{
			get { return true; }
		}

		/// <summary>
		/// True if can enumerate databases on a server. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateDatabases
		{
			get { return true; }
		}

		/// <summary>
		/// True if can launch the administrator for this vendor. This will return true if it can find the admin
		/// program file. In that case the launch could still fail.
		/// </summary>
		public override bool CanLaunchAdministrator
		{
			get
			{
				return GetAdminFilename() != null;
			}
		}

		/// <summary>
		/// True if can run DDL scripts.
		/// </summary>
		public override bool CanRunScripts
		{
			get { return true; }
		}

		/// <summary>
		/// True if can use the WindowsIdentity of the caller eliminating the need to pass a username and password.
		/// </summary>
		public override bool CanUseTrustedConnection
		{
			get { return false; }
		}

		/// <summary>
		/// All servers from this vendor on the network. This may not get all due to routers not passing
		/// on a broadcast or delays in some servers responding.
		/// </summary>
		/// <exception cref="WrDbException">Thrown if this provider does not support enumerate servers.</exception>
		/// <returns>All servers found on the sub-net.</returns>
		public override IWrServer[] GetServers()
		{
			ArrayList rtn = new ArrayList();
			DbDataSourceEnumerator dsEnum = providerFactory.CreateDataSourceEnumerator();
			if (dsEnum != null)
			{
				DataTable servers = dsEnum.GetDataSources();
				int ordServer = servers.Columns.IndexOf("ServerName");
				int ordInstance = servers.Columns.IndexOf("InstanceName");
				Trap.trap(servers.Rows.Count > 0);
				foreach (DataRow row in servers.Rows)
				{
					string server = (string)row[ordServer];
					string instance = (ordInstance == -1) || row.IsNull(ordInstance) ? null : (string)row[ordInstance];
					if (!string.IsNullOrEmpty(instance))
						server += "\\" + instance;
					rtn.Add(new WrMySqlServer(providerFactory, server));
				}
			}

			rtn.Sort();
			return (WrMySqlServer[])rtn.ToArray(typeof(WrMySqlServer));
		}

		/// <summary>
		/// What must be included in the call to execute a stored procedure.
		/// </summary>
		public override SELECT_PARAMS SelectParams
		{
			get { return SELECT_PARAMS.OUT_PARAMS; }
		}

		/// <summary>
		/// Returns true if this connector is installed on the system. To be true the connector must be in the GAC and must
		/// be listed in machine.config.
		/// </summary>
		public override bool IsInstalled
		{
			get
			{
				try
				{
					DbProviderFactory provider = DbProviderFactories.GetFactory(FactoryClass);
					return provider != null;
				}
				catch (Exception)
				{
					return false;
				}
			}
		}

		#endregion

		#region WrVendor methods

		/// <summary>
		/// Returns the parameters from a connection string making the best guess. Will return null for items it could not determine.
		/// </summary>
		/// <param name="connectionString">The connection string to parse.</param>
		/// <returns>The parameters.</returns>
		public override WrConnectionParams ConnectionParams(string connectionString)
		{

			if (string.IsNullOrEmpty(connectionString))
				return new WrConnectionParams();

			DbConnectionStringBuilder builder = providerFactory.CreateConnectionStringBuilder();
			builder.ConnectionString = connectionString;
			return new WrConnectionParams(builder["Server"] as string, builder["Database"] as string, builder["Uid"] as string, builder["Pwd"] as string);
		}

		/// <summary>
		/// A connection string for a database on a server.
		/// </summary>
		/// <param name="server">The server to access (ex: localhost).</param>
		/// <param name="database">The database to connect to.</param>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="showPassword">True to include the password, false to add the password as *****.</param>
		/// <returns>The connection string for this database on this server.</returns>
		public override string ConnectionString(string server, string database, WrCredentials credentials, bool showPassword)
		{
			return ConnectionString(providerFactory, server, database, credentials, showPassword);
		}

		internal static string ConnectionString(DbProviderFactory provider, string server, string database, WrCredentials credentials, bool showPassword)
		{

			DbConnectionStringBuilder connStrBuilder = provider.CreateConnectionStringBuilder();
			if (!string.IsNullOrEmpty(server))
				connStrBuilder.Add("server", server);
			if (!string.IsNullOrEmpty(database))
				connStrBuilder.Add("database", database);
			if (!credentials.UseWindowsIdentity)
			{
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("user id", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("password", showPassword ? credentials.Password : "*****");
			}
			return connStrBuilder.ConnectionString;
		}

		/// <summary>
		/// Returns a new instance of the provider's class that implements the WrCommand class.
		/// </summary>
		/// <returns>A new instance of WrCommand.</returns>
		public override WrCommand CreateCommand()
		{
			Trap.trap();
			return new WrMySqlCommand(providerFactory);
		}

		/// <summary>
		/// Create a server object.
		/// </summary>
		/// <param name="server">The name of the server the database is on.</param>
		public override IWrServer CreateServer(string server)
		{
			return new WrMySqlServer(providerFactory, server);
		}

		/// <summary>
		/// Run a DDL script against a server (specific install on that box).
		/// </summary>
		/// <param name="server">The server to access (ex: localhost).</param>
		/// <param name="database">The database to run the script against (ex: Northwind).</param>
		/// <param name="script">The DDL script.</param>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <exception cref="WrDbException">Thrown if this provider does not support running a script.</exception>
		/// <returns>True if successful.</returns>
		public override bool RunScript(string server, string database, StreamReader script, WrCredentials credentials)
		{

			// read the script
			StringBuilder schema = new StringBuilder();
			string line;
			while ((line = script.ReadLine()) != null)
				schema.Append(line + '\n');
			// no trim - need the final \n after the last GO
			string full = schema.ToString();

			using (IDbConnection sc = providerFactory.CreateConnection())
			{
				sc.ConnectionString = ConnectionString(server, "MYSQL", credentials, true);
				sc.Open();
				using (IDbCommand cmd = sc.CreateCommand())
				{
					cmd.CommandType = CommandType.Text;

					// looking for "statement;"
					int start = 0;
					string delimiter = ";";
					int posSemi = full.IndexOf(';');
					int posDelim = full.IndexOf("delimiter ", StringComparison.OrdinalIgnoreCase);
					while ((posSemi != -1) || (posDelim != -1))
					{
						// standard statement
						if ((posSemi != -1) && ((posSemi < posDelim) || (posDelim == -1)))
						{
							ExecuteStatement(cmd, full.Substring(start, posSemi - start).Trim());
							start = posSemi + 1;
							posSemi = full.IndexOf(delimiter, start);
							continue;
						}

						// handle a delimiter - remove the delimiter parts
						posDelim += 10;
						while ((posDelim < full.Length) && (full[posDelim] <= 32))
							posDelim++;
						int end = posDelim;
						while ((end < full.Length) && (full[end] > 32))
							end++;
						delimiter = full.Substring(posDelim, end - posDelim);
						posSemi = full.IndexOf(delimiter, end);
						posDelim = full.IndexOf("delimiter ", end);
						start = end;
					}


				}
			}
			return true;
		}

		private static void ExecuteStatement(IDbCommand cmd, string statement)
		{
			if (statement.Length == 0)
				return;
			cmd.CommandText = statement;
			cmd.ExecuteNonQuery();
		}

		/// <summary>
		/// Launch the database admin tool.
		/// </summary>
		/// <returns>True if launched successfully.</returns>
		public override bool RunAdministrator()
		{
			Process proc = Process.Start(GetAdminFilename());
			return (proc != null) && (!proc.HasExited);
		}

		private static readonly string[] versions = { "2.1", "2.0", "1.4", "1.3", "1.2", "1.1", "1.0" };
		private static string GetAdminFilename()
		{

			foreach (string ver in versions)
			{
				string exe = GetExe(ver);
				if (exe != null)
				{
					exe = Path.Combine(exe, "MySQLAdministrator.exe");
					if (File.Exists(exe))
						return exe;
				}
			}
			return null;
		}

		private static string GetExe(string ver)
		{

			RegistryKey key = Registry.LocalMachine.OpenSubKey("SOFTWARE\\MySQL AB\\MySQL Administrator " + ver);
			if (key == null)
				return null;
			string exe = (string)key.GetValue("Location");
			if (exe == null)
				return null;
			key.Close();
			return exe;
		}
		#endregion
	}
}
